﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace blog.Dao
{
    public class DBHelper
    {
        static readonly String connstr = "User ID=sa;Password=sa;Initial Catalog=blog;Data Source=localhost;Encrypt=True;TrustServerCertificate=True;";
        //public static SqlConnection GetConn()
        //{           
        //    SqlConnection conn =new SqlConnection(connstr);       
        //    return conn;
        //}
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            SqlDataReader reader = null;
            //创建Connection对象
            SqlConnection sqlConnection = new SqlConnection(connstr);
            

            //获取Command对象
            SqlCommand sqlCommand = buildCommand(sql, sqlConnection, parameters);

            //执行SQL语句
            reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        public static DataSet GetDataSet(string sql, params SqlParameter[] parameters)
        {
            DataSet dataSet = new DataSet();
            //1、创建Connection对象
            using (SqlConnection sqlConnection = new SqlConnection(connstr))
            {
                /* //2、创建Command对象
                 SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                 //3、参数赋值
                 if (parameters != null && parameters.Length > 0)
                 {
                     sqlCommand.Parameters.AddRange(parameters);
                 }*/

                //获取Command对象
                SqlCommand sqlCommand = buildCommand(sql, sqlConnection, parameters);

                //4、创建SqlDataAdapter对象
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                /* //5、打开数据库连接
                 sqlConnection.Open();*/

                //6、把结果及填充到DataSet对象的某张虚拟表（学生表）
                sqlDataAdapter.Fill(dataSet);

                //7、关闭连接
                sqlConnection.Close();
            }
            return dataSet;
        }

        /// <summary>
        /// 通用的增删改方法：insert update delete
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            int count = 0;
            //创建Connection对象
            using (SqlConnection sqlConnection = new SqlConnection(connstr))
            {
                /*//创建Command对象
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                //参数赋值
                if (parameters != null && parameters.Length > 0)
                {
                    sqlCommand.Parameters.AddRange(parameters);
                }
                //打开数据库连接
                sqlConnection.Open();*/

                //获取Command对象
                SqlCommand sqlCommand = buildCommand(sql, sqlConnection, parameters);

                //执行SQL语句
                count = sqlCommand.ExecuteNonQuery();

                //关闭连接
                
            }
            return count;
        }

        /// <summary>
        /// 通用的获取单个值的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            object o = null;
            //创建Connection对象
            using (SqlConnection sqlConnection = new SqlConnection(connstr))
            {
                /* //创建Command对象
                 SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);

                 //参数赋值
                 if (parameters!=null&& parameters.Length>0)
                 {
                     sqlCommand.Parameters.AddRange(parameters);
                 }
                 //打开数据库连接
                 sqlConnection.Open();*/

                //获取Command对象
                SqlCommand sqlCommand = buildCommand(sql, sqlConnection, parameters);

                //执行SQL语句
                o = sqlCommand.ExecuteScalar();

                //关闭连接
                sqlConnection.Close();
            }
            return o;
        }
        //构造Command对象
        public static SqlCommand buildCommand(string sql, SqlConnection sqlConnection, SqlParameter[] parameters)
        {
            //创建Command对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

            //参数赋值
            if (parameters != null && parameters.Length > 0)
            {
                sqlCommand.Parameters.AddRange(parameters);
            }
            if (sqlConnection.State == ConnectionState.Closed || sqlConnection.State == ConnectionState.Broken)
            {
                //打开数据库连接
                sqlConnection.Open();
            }
            return sqlCommand;
        }
    }
        
    
}
